#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/

$HIVE_CMD -S -f "/root/Liam/online_edu/交付文件/create_test.sql"


${HIVE_HOME} -S -e "
-- DWB建表
create database if not exists edu_dwb;
use edu_dwb;
-- 大宽表
drop table if exists edu_dwb.customer_relationship_detail;
create table if not exists edu_dwb.customer_relationship_detail(
       -- 指标
        id                      int         comment '指标id--意向id',
       -- 时间维度
        create_date_time        string      comment '创建时间，时间维度',
--         update_date_time,
        -- 线上线下
        origin_type             string      comment '来源类型，线上或线下',
        -- 新老学员
        customer_state          string      comment '客户状态，新学员或老学员',
        -- 各地区
        area                    string      comment '国家 省 市',
        -- 各学科
        subject_name            string      comment '课程名称',
        -- 各校区
        school_name             string      comment '校区名称',
        -- 各来源渠道
        origin_channel          string      comment '来源渠道',
        -- 各咨询中心
        department_name         string      comment '咨询中心(员工所在部门)'
)comment '客户意向明细宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

drop table if exists edu_dwb.customer_clue_detail;
create table if not exists edu_dwb.customer_clue_detail(
       -- 指标
       id                      int         comment '指标id--线索id',
       -- 线索信息
       appeal_status           string      comment '线索信息，！=1均为有效线索',
       -- 时间维度
       create_date_time        string      comment '创建时间，时间维度',
       -- 线上线下
       origin_type             string      comment '来源类型，线上或线下',
       -- 新老学员
       customer_state          string      comment '客户状态，新学员或老学员'
)comment '有效线索明细宽表'
row format delimited
fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY')
;

"

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
-- DWD_to_DWB_presto
insert into edu_dwb.customer_relationship_detail
select
       -- 指标
        fcr.id,
       -- 时间维度
        fcr.create_date,
--         fcr.update_date_time,
        -- 线上线下
        fcr.origin_type,
        -- 新老学员
        dcc.customer_state,
        -- 各地区
        dc.area,
        -- 各学科
        dit.name,
        -- 各校区
        dis.name,
        -- 各来源渠道
        fcr.origin_channel,
        -- 各咨询中心
        dsd.name
from edu_dwd.fact_customer_relationship fcr
left join edu_dwd.dim_customer dc on fcr.customer_id = dc.id
left join edu_dwd.dim_itcast_school dis on fcr.itcast_school_id = dis.id
left join edu_dwd.dim_itcast_subject dit on fcr.itcast_subject_id = dit.id
left join edu_dwd.dim_employee de on fcr.creator = de.id
left join edu_dwd.dim_scrm_department dsd on de.scrm_department_id = dsd.id
left join edu_dwd.dim_customer_clue dcc on fcr.id = dcc.customer_relationship_id
;

insert into edu_dwb.customer_clue_detail
select
        -- 指标
        fcc.id,
        case when dca.appeal_status != 1 then '有效'
             when dca.appeal_status = 1 then '无效'
             end as appeal_status,
        -- 时间维度
        dcr.create_date_time,
        -- 线上线下
        dcr.origin_type,
        -- 新老学员
        fcc.customer_state
from edu_dwd.fact_customer_clue fcc
left join edu_dwd.dim_customer_relationship dcr on fcc.customer_relationship_id = dcr.id
left join edu_dwd.dim_customer_appeal dca on fcc.customer_relationship_id = dca.customer_relationship_first_id
;
"